﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HKEA.CMS.Core.Models;
namespace HKEA.CMS.Core.Utility.Resource
{
    public class SearchResuleMethod
    {
        #region 
        /// <summary>
        /// 原始方案的SQL查询语句
        /// </summary>
        /// <param name="ent">查询条件实体</param>
        /// <returns></returns>
        public static string SearchString(Search_Cust_Info ent)
        {
            StringBuilder str = new StringBuilder();
            string SqlStr = "SELECT  DISTINCT "
                + " \n  Customer.CustomerUID, Customer.CustomerID,CustType.CustType_Chi,Customer.Name_Eng,Customer.Name_Chi,Customer.IsMember,Customer.IsGCMember,Customer.IsVIP,Cust_Branch.BranchName_Chi ,SUBSTRING(TABLe_Industry.ProductCatID,1,1) AS Industry_Eng,ContactPerson.FirstName_Chi,ContactPerson.LastName_Chi,ContactPerson.FirstName_Eng,ContactPerson.LastName_Eng,ContactPerson.ChristianName,Cust_ContactPerson.Position_Eng,addre_Person.Flat_Bldg_Eng,addre_Branch.Flat_Bldg_Chi,addre_Person.Street_Rd_Eng,addre_Person.Area_Eng,addre_Person.District_Eng,addre_Person.City_Eng,Country_p.Country_Eng,CP_ContactInfo.Tel1,CP_ContactInfo.Tel2,CP_ContactInfo.Fax1,CP_ContactInfo.Fax2,CP_ContactInfo.Email1"
                + " \n   FROM dbo.tblCustomer AS Customer"
                + " \n   LEFT JOIN  dbo.tblCust_Member AS Cust_Member"
                + " \n   ON Customer.CustomerUID = Cust_Member.CustomerUID"
                //+ " \n   LEFT JOIN dbo.mtblMembershipStatus AS MembershipStatus"
                //+ " \n   ON Cust_Member.MemStatusUID = MembershipStatus.MemStatusUID"
                //+ " \n   LEFT JOIN dbo.tblCust_BusNature AS Cust_BuNature"
                //+ " \n   ON Customer.CustomerUID=Cust_BuNature.CustomerUID"
                //+ " \n   LEFT JOIN dbo.mtblBusinessNature AS BusinessNature"
                //+ " \n   ON Cust_BuNature.BusNatureUID = BusinessNature.BusNatureUID"
                + " \n   LEFT JOIN dbo.mtblCustType AS CustType"
                + " \n   ON Customer.CustTypeUID=CustType.CustTypeUID"
                + " \n   LEFT JOIN dbo.mtblBrand AS Brand"
                + " \n   ON Customer.CustomerUID=Brand.CustomerUID"
                + " \n   LEFT JOIN dbo.tblBrand_ProdCat AS Brand_ProdCat"
                + " \n   ON Brand.BrandUID = Brand_ProdCat.BrandUID"
                + " \n   LEFT JOIN dbo.mtblProductCat AS ProductCat"
                + " \n   ON Brand_ProdCat.ProductCatUID = ProductCat.ProductCatUID"
                //+ " \n   LEFT JOIN tblCust_ProdCat AS Cust_ProdCat"
                //+ " \n   ON Customer.CustomerUID=Cust_ProdCat.CustomerUID"
                + " \n   LEFT JOIN ("
                + " \n   SELECT DISTINCT ProductCat.ProductCatID,ProductSubCat.ProductCatUID,ProductSubCat.ProductSubCatUID,Cust_ProdCat.CustomerUID,Cust_ProdCatUID"
                + " \n   FROM  dbo.mtblProductSubCat AS ProductSubCat"
                //+ " \n   LEFT JOIN dbo.mtblProdCat_Industry AS ProdCat_Industry"
                //+ " \n   ON ProductSubCat.ProductSubCatUID=ProdCat_Industry.ProductSubCatUID"
                + " \n   LEFT JOIN dbo.mtblProductCat AS ProductCat"
                + " \n   ON ProductCat.ProductCatUID=ProductSubCat.ProductCatUID"
                + " \n   LEFT JOIN dbo.tblCust_ProdCat AS Cust_ProdCat"
                + " \n   ON ProductSubCat.ProductSubCatUID = Cust_ProdCat.ProductSubCatUID) AS TABLe_Industry"
                + " \n   ON Customer.CustomerUID=TABLe_Industry.CustomerUID"
                + " \n   LEFT JOIN dbo.tblCust_Branch AS Cust_Branch"
                + " \n   ON Customer.CustomerUID=Cust_Branch.CustomerUID"
                + " \n   LEFT JOIN dbo.tblAddress AS addre_Branch"
                + " \n   ON Cust_Branch.AddressUID=addre_Branch.AddressUID"
                + " \n   LEFT JOIN mtblCountry AS Country_B"
                + " \n   ON addre_Branch.CountryUID = Country_B.CountryUID"
                + " \n   LEFT JOIN dbo.tblContactInfo AS C_B_ContactInfo"
                + " \n   ON Cust_Branch.ContactInfoUID=C_B_ContactInfo.ContactInfoUID"
                + " \n   LEFT JOIN dbo.tblCust_ContactPerson AS Cust_ContactPerson"
                + " \n   ON Cust_ContactPerson.CustomerUID=Customer.CustomerUID"
                + " \n   LEFT JOIN dbo.tblContactPerson AS ContactPerson"
                + " \n   ON ContactPerson.ContactPersonUID=Cust_ContactPerson.ContactPersonUID"
                + " \n   LEFT JOIN dbo.tblAddress AS addre_Person"
                + " \n   ON addre_Person.AddressUID=ContactPerson.AddressUID"
                + " \n   LEFT JOIN dbo.mtblCountry AS Country_p"
                + " \n   ON Country_p.CountryUID=addre_Person.CountryUID"
                + " \n   LEFT JOIN dbo.tblContactInfo AS CP_ContactInfo"
                + " \n   ON ContactPerson.ContactInfoUID=CP_ContactInfo.ContactInfoUID"
                + " \n   LEFT JOIN dbo.tblCPerson_CommMember AS CPerson_CommMember"
                + " \n   ON CPerson_CommMember.ContactPersonUID=ContactPerson.ContactPersonUID"
                + " \n   LEFT JOIN dbo.mtblCommPosition AS CommPosition"
                + " \n   ON CommPosition.CommPositionUID=CPerson_CommMember.CommPositionUID"
                //+ " \n   LEFT JOIN dbo.mtblCommType AS CommType"
                //+ " \n   ON CommType.CommTypeUID=CPerson_CommMember.CommTypeUID"
                + " \n   LEFT JOIN dbo.tblCPerson_JobNature AS CP_JobNature"
                + " \n   ON CP_JobNature.ContactPersonUID=ContactPerson.ContactPersonUID";
                //+ " \n   LEFT JOIN dbo.mtblJobNature AS JobNature"
                //+ " \n   ON JobNature.JobNatureUID= CP_JobNature.JobNatureUID"
                //+ " \n   LEFT JOIN dbo.mtblSalutation AS Salutation"
                //+ " \n   ON ContactPerson.SalutationUID=Salutation.SalutationUID";
                //+ " \n   LEFT JOIN dbo.tblCPerson_Attribute AS CP_Attribute"
                //+ " \n   ON ContactPerson.ContactPersonUID=CP_Attribute.ContactPersonUID";
                //+ " \n   LEFT JOIN dbo.mtblAttribute AS Attribute"
                //+ " \n   ON CP_Attribute.AttributeUID = Attribute.AttributeUID";
                //+" \n   LEFT JOIN dbo.tblCust_Market AS Cust_Market"
                //+ " \n   ON Customer.CustomerUID= Cust_Market.CustomerUID";
                //+" \n   LEFT JOIN dbo.mtblCountry AS Market_Country"
                //+" \n   ON Cust_Market.CountryUID=Market_Country.CountryUID";

            string strSQL_Info = " where";

            if (ent.CustomerName != null & ent.CustomerName != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT CustomerUID FROM dbo.tblCustomer  WHERE CONTAINS((Name_Chi,Name_Eng),'\"" + ent.CustomerName + "\"')) and ";
            }
            if (ent.CustomerID != null & ent.CustomerID != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerID='" + ent.CustomerID + "' and ";
            }
            if (ent.CustomerType != null & ent.CustomerType != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT CustomerUID FROM dbo.tblCustomer WHERE CustTypeUID IN (SELECT DISTINCT CustTypeUID FROM dbo.mtblCustType WHERE CONTAINS((CustType_Eng,CustType_Chi),'\"" + ent.CustomerType + "\"'))) and ";
            }
            if (ent.MemberShipSince != null & ent.MemberShipSince != "")
            {
                strSQL_Info = strSQL_Info + " Customer.EntryDate='" + ent.MemberShipSince + "'  and ";
            }
            if (ent.MemberShipStatus != null & ent.MemberShipStatus != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT CustomerUID FROM dbo.tblCust_Member WHERE MemStatusUID IN (SELECT DISTINCT MemStatusUID FROM dbo.mtblMembershipStatus where CONTAINS((MemStatus_Eng,MemStatus_Chi),'\"" + ent.MemberShipStatus + "\"'))) and ";
            }
            if (ent.BrandName != null & ent.BrandName != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.mtblBrand WHERE  CONTAINS((BrandName_Chi,BrandName_Eng),'\"" + ent.BrandName + "\"')) and ";
            }
            if (ent.CustomerIntroduction != null & ent.CustomerIntroduction != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCustomer WHERE CONTAINS((Intro_Eng,Intro_Chi),'\"" + ent.CustomerIntroduction + "\"')) and ";
            }
            if (ent.NoOfEmployee != null & ent.NoOfEmployee != "")
            {
                strSQL_Info = strSQL_Info + " Customer.NoOfEmp='" + ent.NoOfEmployee + "' and ";
            }
            if (ent.YearEstablished != null & ent.YearEstablished != "")
            {
                strSQL_Info = strSQL_Info + " Customer.EstDate='" + ent.YearEstablished + "' and ";
            }
            if (ent.BR != null & ent.BR != "")
            {
                strSQL_Info = strSQL_Info + " Customer.BR='" + ent.BR + "' and ";
            }
            if (ent.HKIDPassportNo != null & ent.HKIDPassportNo != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.HKID_PassportID='" + ent.HKIDPassportNo + "' OR Customer.HKID_PassportNo='" + ent.HKIDPassportNo + "') and ";
            }
            if (ent.GC != null & ent.GC != "")
            {
                strSQL_Info = strSQL_Info + " Customer.IsGCMember='" + ent.GC + "' and ";
            }
            if (ent.VIP != null & ent.VIP != "")
            {
                strSQL_Info = strSQL_Info + " Customer.IsVIP='" + ent.VIP + "' and ";
            }
            if (ent.HQLocatedInHk != null & ent.HQLocatedInHk != "")
            {
                strSQL_Info = strSQL_Info + " Customer.HQLocatedInHk='" + ent.HQLocatedInHk + "'  and ";
            }
            if (ent.Address != null & ent.Address != "")
            {
                strSQL_Info = strSQL_Info + " (Cust_Branch.CustBranchUID IN(SELECT DISTINCT CustBranchUID FROM dbo.tblCust_Branch WHERE AddressUID IN (SELECT DISTINCT AddressUID FROM dbo.tblAddress where CONTAINS((Flat_Bldg_Eng,Street_Rd_Eng,Area_Eng,District_Eng,City_Eng,Province_Eng,Flat_Bldg_Chi,Street_Rd_Chi,Area_Chi,District_Chi,City_Chi,Province_Chi,FullAdd_Eng,FullAdd_Chi),'\"" + ent.Address + "\"'))) OR Cust_ContactPerson.ContactPersonUID IN(SELECT DISTINCT ContactPersonUID FROM dbo.tblContactPerson WHERE AddressUID IN (SELECT AddressUID FROM dbo.tblAddress where Contains((Flat_Bldg_Eng,Street_Rd_Eng,Area_Eng,District_Eng,City_Eng,Province_Eng,Flat_Bldg_Chi,Street_Rd_Chi,Area_Chi,District_Chi,City_Chi,Province_Chi,FullAdd_Eng,FullAdd_Chi),'\"" + ent.Address + "\"'))))  and ";
            }
            if (ent.Country != null & ent.Country != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCust_Market where CountryUID in(SELECT CountryUID FROM  dbo.mtblCountry WHERE CONTAINS((Country_Eng, Country_Chi ),'\"" + ent.Country + "\"'))) and ";
            }
            if (ent.Position != null & ent.Position != "")
            {
                strSQL_Info = strSQL_Info + " CustContactUID IN (SELECT CustContactUID FROM dbo.tblCust_ContactPerson WHERE CONTAINS((Position_Chi,Position_Eng),'\"" + ent.Position + "\"')) and ";
            }
            if (ent.ContactPersonName != null & ent.ContactPersonName != "")
            {
                strSQL_Info = strSQL_Info + " ContactPerson.ContactPersonUID IN (select table1.ContactPersonUID from (select DISTINCT ContactPersonUID ,(ISNULL(LastName_Chi,'''')+ISNULL(FirstName_Chi,''''))AS FirstAndLastName_Chi,(ISNULL(ChristianName,'''')+ISNULL(FirstName_Eng,'''')+ISNULL(LastName_Eng,''''))AS FirstAndLastName_Eng from dbo.tblContactPerson) table1 where  table1.FirstAndLastName_Chi like '%" + ent.ContactPersonName + "%' OR table1.FirstAndLastName_Eng like '%" + ent.ContactPersonName + "%') and ";
            }
            if (ent.Salutation != null & ent.Salutation != "")
            {
                strSQL_Info = strSQL_Info + " ContactPerson.SalutationUID IN (SELECT DISTINCT SalutationUID FROM dbo.mtblSalutation where CONTAINS((Salutation_Eng,Salutation_Chi),'\"" + ent.Salutation + "\"')) and ";
            }
            if (ent.CommitteeType != null & ent.CommitteeType != "")
            {
                strSQL_Info = strSQL_Info + " Cperson_CommMemberUID IN (SELECT Cperson_CommMemberUID FROM dbo.tblCPerson_CommMember WHERE CommTypeUID IN(SELECT CommTypeUID FROM dbo.mtblCommType WHERE CONTAINS((CommType_Eng,CommType_Chi),'\"" + ent.CommitteeType + "\"'))) and ";
            }
            if (ent.CommitteePosition != null & ent.CommitteePosition != "")
            {
                strSQL_Info = strSQL_Info + " CPerson_CommMemberUID IN (SELECT Cperson_CommMemberUID FROM dbo.tblCPerson_CommMember WHERE CommPositionUID IN(SELECT CommPositionUID FROM dbo.mtblCommPosition WHERE CONTAINS((CommPosition_Eng,CommPosition_Chi),'\"" + ent.CommitteePosition + "\"'))) and ";
            }
            if (ent.YearTerm != null & ent.YearTerm != "")
            {
                strSQL_Info = strSQL_Info + " Cperson_CommMemberUID in (SELECT Cperson_CommMemberUID FROM dbo.tblCPerson_CommMember WHERE CONTAINS([YEAR],'\"" + ent.YearTerm + "\"')) and ";
            }
            if (ent.ProductSpecifictions != null & ent.ProductSpecifictions != "")
            {
                strSQL_Info = strSQL_Info + " TABLe_Industry.Cust_ProdCatUID IN (SELECT DISTINCT Cust_ProdCatUID FROM dbo.tblCust_ProdCat WHERE ProductSubCatUID IN (SELECT ProductSubCatUID FROM dbo.mtblProductSubCat where CONTAINS	((ProductSubCat_Eng,ProductSubCat_Chi),'\""+ent.ProductSpecifictions+"\"'))) and ";
            }
            if (ent.BranchAddress != null & ent.BranchAddress != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT CustomerUID FROM tblCust_Market WHERE CountryUID in (SELECT DISTINCT CountryUID FROM dbo.mtblCountry where CONTAINS((Country_Chi,Country_Eng),'\"" + ent.BranchAddress + "\"'))) and ";
            }
            if (ent.BranchName != null & ent.BranchName != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT CustomerUID FROM dbo.tblCust_Branch WHERE CONTAINS((BranchName_Eng,BranchName_Chi),'\""+ent.BranchName+"\"')) and ";
            }
            if (ent.Email != null & ent.Email != "")
            {
                strSQL_Info = strSQL_Info + "(ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE CONTAINS((Email1,Email2,Email3,Email4,Email5),'\"" + ent.Email + "\"')) or Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE CONTAINS ((Email1,Email2,Email3,Email4,Email5),'\"" + ent.Email + "\"'))) and ";
            }

            if (ent.EmailUnsubscribe != null & ent.EmailUnsubscribe != "")
            {
                strSQL_Info = strSQL_Info +
               " (C_B_ContactInfo.Email1_IsSubscribe='" + ent.EmailUnsubscribe+"'"
                + " OR C_B_ContactInfo.Email2_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR C_B_ContactInfo.Email3_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR C_B_ContactInfo.Email4_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR C_B_ContactInfo.Email5_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR CP_ContactInfo.Email1_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR CP_ContactInfo.Email2_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR CP_ContactInfo.Email3_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR CP_ContactInfo.Email4_IsSubscribe='" + ent.EmailUnsubscribe + "'"
                + " OR CP_ContactInfo.Email5_IsSubscribe='" + ent.EmailUnsubscribe + "') and ";
            }


            if (ent.Fax != null & ent.Fax != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE CONTAINS((Fax1,Fax2,Fax3,Fax4,Fax5),'\"" + ent.Fax + "\"')) or Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE CONTAINS ((Fax1,Fax2,Fax3,Fax4,Fax5),'\"" + ent.Fax + "\"'))) and ";
            }

            if (ent.FaxUnsubscribe != null & ent.FaxUnsubscribe != "")
            {
                strSQL_Info = strSQL_Info +
               " (C_B_ContactInfo.Fax1_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR C_B_ContactInfo.Fax2_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR C_B_ContactInfo.Fax3_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR C_B_ContactInfo.Fax4_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR C_B_ContactInfo.Fax5_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR CP_ContactInfo.Fax1_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR CP_ContactInfo.Fax2_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR CP_ContactInfo.Fax3_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR CP_ContactInfo.Fax4_IsSubscribe='" + ent.FaxUnsubscribe + "'"
                + " OR CP_ContactInfo.Fax5_IsSubscribe='" + ent.FaxUnsubscribe + "') and ";
            }
            if (ent.Fax_IsDNC != null & ent.Fax_IsDNC != "")
            {
                strSQL_Info = strSQL_Info +
               " (C_B_ContactInfo.Fax1_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR C_B_ContactInfo.Fax2_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR C_B_ContactInfo.Fax3_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR C_B_ContactInfo.Fax4_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR C_B_ContactInfo.Fax5_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR CP_ContactInfo.Fax1_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR CP_ContactInfo.Fax2_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR CP_ContactInfo.Fax3_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR CP_ContactInfo.Fax4_IsDNC='" + ent.Fax_IsDNC + "'"
                + " OR CP_ContactInfo.Fax5_IsDNC='" + ent.Fax_IsDNC + "') and ";
            }
            if (ent.Tel != null & ent.Tel != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE CONTAINS((Tel1,Tel2,Tel3,Tel4,Tel5),'\"" + ent.Tel + "\"')) or Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE CONTAINS ((Tel1,Tel2,Tel3,Tel4,Tel5),'\"" + ent.Tel + "\"'))) and ";
            }

            if (ent.Tel_IsSubscribe != null & ent.Tel_IsSubscribe != "")
            {
                strSQL_Info = strSQL_Info +
               " (C_B_ContactInfo.Tel1_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR C_B_ContactInfo.Tel2_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR C_B_ContactInfo.Tel3_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR C_B_ContactInfo.Tel4_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR C_B_ContactInfo.Tel5_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR CP_ContactInfo.Tel1_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR CP_ContactInfo.Tel2_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR CP_ContactInfo.Tel3_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR CP_ContactInfo.Tel4_IsSubscribe='" + ent.Tel_IsSubscribe + "'"
                + " OR CP_ContactInfo.Tel5_IsSubscribe='" + ent.Tel_IsSubscribe + "') and ";
            }

            if (ent.Tel_IsDNC != null & ent.Tel_IsDNC != "")
            {
                strSQL_Info = strSQL_Info +
               " (C_B_ContactInfo.Tel1_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR C_B_ContactInfo.Tel2_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR C_B_ContactInfo.Tel3_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR C_B_ContactInfo.Tel4_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR C_B_ContactInfo.Tel5_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR CP_ContactInfo.Tel1_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR CP_ContactInfo.Tel2_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR CP_ContactInfo.Tel3_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR CP_ContactInfo.Tel4_IsDNC='" + ent.Tel_IsDNC + "'"
                + " OR CP_ContactInfo.Tel5_IsDNC='" + ent.Tel_IsDNC + "') and ";
            }

            if (ent.Attribute != null & ent.Attribute != "")
            {
                strSQL_Info = strSQL_Info + " Cust_ContactPerson.ContactPersonUID IN(SELECT DISTINCT ContactPersonUID FROM dbo.tblCPerson_Attribute WHERE AttributeUID IN (" + ent.Attribute + ")) and ";
            }

            if (ent.JobNaturexdcc != null & ent.JobNaturexdcc != "")
            {
                strSQL_Info = strSQL_Info + " Cust_ContactPerson.ContactPersonUID IN(SELECT DISTINCT ContactPersonUID FROM dbo.tblCPerson_JobNature WHERE JobNatureUID IN (" + ent.JobNaturexdcc + ")) and ";
            }
            if (ent.BusinessNatur != null & ent.BusinessNatur != "")
            {
                strSQL_Info = strSQL_Info + ent.BusinessNatur + " and ";
            }
            if (ent.ProductCategory != null & ent.ProductCategory != "")
            {
                strSQL_Info = strSQL_Info + ent.ProductCategory + " and ";
            }
            if (ent.MainMarket != null & ent.MainMarket != "")
            {
                strSQL_Info = strSQL_Info + ent.MainMarket + " and ";
            }

            strSQL_Info = strSQL_Info.Substring(0, strSQL_Info.Length - 5);
            string strSQL_C = " order by Customer.CustomerID,CustType.CustType_Chi,Customer.Name_Eng,ContactPerson.FirstName_Chi,ContactPerson.LastName_Chi,ContactPerson.FirstName_Eng,ContactPerson.LastName_Eng,ContactPerson.ChristianName";
            str.Append(SqlStr);
            str.Append(strSQL_Info + strSQL_C);
            return str.ToString();
        }
        #endregion


        #region 
        /// <summary>
        /// 参数化的查询
        /// </summary>
        /// <param name="ent">查询条件实体</param>
        /// <returns></returns>
        #region 数据通过标识Deleted表示删除的数据,所以原来的写法就不能再用,但是防止以后又改回来,所以保留语句
        //+ " \n   FROM dbo.tblCustomer AS Customer"
        //+ " \n   LEFT JOIN  dbo.tblCust_Member AS Cust_Member"
        //+ " \n   LEFT JOIN dbo.mtblCustType AS CustType"
        //+ " \n   LEFT JOIN dbo.tblBrand_ProdCat AS Brand_ProdCat"
        //+ " \n   LEFT JOIN dbo.tblCust_Branch AS Cust_Branch"
        //+ " \n   LEFT JOIN dbo.tblCust_ContactPerson AS Cust_ContactPerson"
        //+ " \n   LEFT JOIN dbo.tblCPerson_CommMember AS CPerson_CommMember"
        //+ " \n   LEFT JOIN dbo.mtblCommPosition AS CommPosition"
        //+ " \n   LEFT JOIN dbo.tblCPerson_JobNature AS CP_JobNature"
        #endregion
        public static string SqlParamString(Search_Cust_Info ent)
        {
            StringBuilder str = new StringBuilder();
            string SqlStr = "SELECT  DISTINCT "
                + " \n  Customer.CustomerUID, Customer.CustomerID,CustType.CustType_Chi,Customer.Name_Eng,Customer.Name_Chi,Customer.IsMember,Customer.IsGCMember,Customer.IsVIP,Cust_Branch.BranchName_Chi ,SUBSTRING(TABLe_Industry.ProductCatID,1,1) AS Industry_Eng,ContactPerson.FirstName_Chi,ContactPerson.LastName_Chi,ContactPerson.FirstName_Eng,ContactPerson.LastName_Eng,ContactPerson.ChristianName,Cust_ContactPerson.Position_Eng,addre_Person.Flat_Bldg_Eng,addre_Branch.Flat_Bldg_Chi,addre_Person.Street_Rd_Eng,addre_Person.Area_Eng,addre_Person.District_Eng,addre_Person.City_Eng,Country_p.Country_Eng,CP_ContactInfo.Tel1,CP_ContactInfo.Tel2,CP_ContactInfo.Fax1,CP_ContactInfo.Fax2,CP_ContactInfo.Email1"
                + " \n   FROM (select * from dbo.tblCustomer where Deleted=0) AS Customer"                
                + " \n   LEFT JOIN (select * from dbo.tblCust_Member where Deleted=0) AS Cust_Member"
                + " \n   ON Customer.CustomerUID = Cust_Member.CustomerUID"                
                + " \n   LEFT JOIN (select * from dbo.mtblCustType where Deleted=0) AS CustType"
                + " \n   ON Customer.CustTypeUID=CustType.CustTypeUID"
                + " \n   LEFT JOIN dbo.mtblBrand AS Brand"
                + " \n   ON Customer.CustomerUID=Brand.CustomerUID"                
                + " \n   LEFT JOIN (select * from dbo.tblBrand_ProdCat where Deleted=0) AS Brand_ProdCat"
                + " \n   ON Brand.BrandUID = Brand_ProdCat.BrandUID"
                + " \n   LEFT JOIN dbo.mtblProductCat AS ProductCat"
                + " \n   ON Brand_ProdCat.ProductCatUID = ProductCat.ProductCatUID"
                + " \n   LEFT JOIN ("
                + " \n   SELECT DISTINCT ProductCat.ProductCatID,ProductSubCat.ProductCatUID,ProductSubCat.ProductSubCatUID,Cust_ProdCat.CustomerUID,Cust_ProdCatUID"
                + " \n   FROM  dbo.mtblProductSubCat AS ProductSubCat"
                + " \n   LEFT JOIN dbo.mtblProductCat AS ProductCat"
                + " \n   ON ProductCat.ProductCatUID=ProductSubCat.ProductCatUID"
                + " \n   LEFT JOIN dbo.tblCust_ProdCat AS Cust_ProdCat"
                + " \n   ON ProductSubCat.ProductSubCatUID = Cust_ProdCat.ProductSubCatUID where ProductSubCat.Deleted=0 and ProductCat.Deleted=0 AND Cust_ProdCat.Deleted=0) AS TABLe_Industry"
                + " \n   ON Customer.CustomerUID=TABLe_Industry.CustomerUID"                
                + " \n   LEFT JOIN (select * from dbo.tblCust_Branch where Deleted=0) AS Cust_Branch"
                + " \n   ON Customer.CustomerUID=Cust_Branch.CustomerUID"
                + " \n   LEFT JOIN dbo.tblAddress AS addre_Branch"
                + " \n   ON Cust_Branch.AddressUID=addre_Branch.AddressUID"
                + " \n   LEFT JOIN (SELECT CountryUID,Country_Eng,Country_Chi,Country_Code ,AreaUID FROM mtblCountry WHERE Deleted=0) AS Country_B"
                + " \n   ON addre_Branch.CountryUID = Country_B.CountryUID"
                + " \n   LEFT JOIN dbo.tblContactInfo AS C_B_ContactInfo"
                + " \n   ON Cust_Branch.ContactInfoUID=C_B_ContactInfo.ContactInfoUID"                
                + " \n   LEFT JOIN (select * from dbo.tblCust_ContactPerson where Deleted=0) AS Cust_ContactPerson"
                + " \n   ON Cust_ContactPerson.CustomerUID=Customer.CustomerUID"
                + " \n   LEFT JOIN dbo.tblContactPerson AS ContactPerson"
                + " \n   ON ContactPerson.ContactPersonUID=Cust_ContactPerson.ContactPersonUID"
                + " \n   LEFT JOIN dbo.tblAddress AS addre_Person"
                + " \n   ON addre_Person.AddressUID=ContactPerson.AddressUID"
                + " \n   LEFT JOIN (SELECT CountryUID,Country_Eng,Country_Chi,Country_Code ,AreaUID FROM mtblCountry WHERE Deleted=0) AS Country_p"
                + " \n   ON Country_p.CountryUID=addre_Person.CountryUID"
                + " \n   LEFT JOIN dbo.tblContactInfo AS CP_ContactInfo"
                + " \n   ON ContactPerson.ContactInfoUID=CP_ContactInfo.ContactInfoUID"                
                + " \n   LEFT JOIN (select * from dbo.tblCPerson_CommMember where Deleted=0) AS CPerson_CommMember"
                + " \n   ON CPerson_CommMember.ContactPersonUID=ContactPerson.ContactPersonUID"                
                + " \n   LEFT JOIN (select * from dbo.mtblCommPosition where Deleted=0) AS CommPosition"
                + " \n   ON CommPosition.CommPositionUID=CPerson_CommMember.CommPositionUID"                
                + " \n   LEFT JOIN (select * from dbo.tblCPerson_JobNature where Deleted=0) AS CP_JobNature"
                + " \n   ON CP_JobNature.ContactPersonUID=ContactPerson.ContactPersonUID";

            string strSQL_Info = " where";

            if (ent.CustomerName != null & ent.CustomerName != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT CustomerUID FROM dbo.tblCustomer  WHERE Deleted=0 and CONTAINS((Name_Chi,Name_Eng),@CustomerName)) and ";
            }
            if (ent.CustomerID != null & ent.CustomerID != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCustomer  WHERE Deleted=0 and CustomerID LIKE '%'+@CustomerID+'%') and ";
            }
            if (ent.CustomerType != null & ent.CustomerType != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT CustomerUID FROM dbo.tblCustomer WHERE Deleted=0 and CustTypeUID = @CustomerType) and ";//SELECT DISTINCT CustTypeUID FROM dbo.mtblCustType WHERE CONTAINS((CustType_Eng,CustType_Chi),@CustomerType))
            }
            if (ent.MemberShipSince != null & ent.MemberShipSince != "")
            {
                //if (ent.MemberShipSince.Length == 4) {
                //    strSQL_Info = strSQL_Info + "  Customer.EntryDate LIKE'%'+@MemberShipSince+'%' and ";
                //}
                //else if (ent.MemberShipSince.Length > 4 & ent.MemberShipSince.Length < 8) {
                    strSQL_Info = strSQL_Info + " (year(Customer.EntryDate)=@YearEntry and month(Customer.EntryDate)=@MothEntry) and "; ;
                //}
                //else if (ent.MemberShipSince.Length > 7)
                //{
                //    strSQL_Info = strSQL_Info + " Customer.EntryDate=@MemberShipSince  and ";
                //}                
            }
            if (ent.MemberShipStatus != null & ent.MemberShipStatus != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT CustomerUID FROM dbo.tblCust_Member WHERE Deleted=0 and MemStatusUID IN (SELECT DISTINCT MemStatusUID FROM dbo.mtblMembershipStatus where Deleted=0 and CONTAINS((MemStatus_Eng,MemStatus_Chi),@MemberShipStatus))) and ";
            }
            if (ent.BrandName != null & ent.BrandName != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.mtblBrand WHERE  CONTAINS((BrandName_Chi,BrandName_Eng),@BrandName)) and ";
            }
            if (ent.CustomerIntroduction != null & ent.CustomerIntroduction != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCustomer WHERE Deleted=0 and CONTAINS((Intro_Eng,Intro_Chi),@CustomerIntroduction)) and ";
            }
            if (ent.NoOfEmployee != null & ent.NoOfEmployee != "")
            {
                strSQL_Info = strSQL_Info + " (Customer.NoOfEmp=@NoOfEmployee or Cust_Branch.NOfEmp=@NoOfEmployee) and ";
            }
            if (ent.YearEstablished != null & ent.YearEstablished != "")
            {
                if (ent.YearEstablished.Length == 4)
                {
                    strSQL_Info = strSQL_Info + "  Customer.EstDate LIKE'%'+@YearEstablished+'%' and ";
                }
                else if (ent.YearEstablished.Length > 4 & ent.YearEstablished.Length < 8)
                {
                    strSQL_Info = strSQL_Info + " (year(Customer.EstDate)=@YearEst and month(Customer.EstDate)=@MothEst) and ";
                }
                else if (ent.YearEstablished.Length > 7) {
                    strSQL_Info = strSQL_Info + "  Customer.EstDate=@YearEstablished and ";
                }
            }
            if (ent.BR != null & ent.BR != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT CustomerUID FROM dbo.tblCustomer  WHERE Deleted=0 and CONTAINS(BR,@BR)) and ";
            }
            if (ent.HKIDPassportNo != null & ent.HKIDPassportNo != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.HKID_PassportID like '%'+@HKIDPassportNo+'%' OR Customer.HKID_PassportNo like '%'+@HKIDPassportNo+'%') and ";
            }
            if (ent.GC != null & ent.GC != "")
            {
                strSQL_Info = strSQL_Info + " Customer.IsGCMember=@GC and ";
            }
            if (ent.VIP != null & ent.VIP != "")
            {
                strSQL_Info = strSQL_Info + " Customer.IsVIP=@VIP and ";
            }
            if (ent.HQLocatedInHk != null & ent.HQLocatedInHk != "")
            {
                strSQL_Info = strSQL_Info + " Customer.HQLocatedInHk=@HQLocatedInHk  and ";
            }
            if (ent.Address != null & ent.Address != "")
            {                
                strSQL_Info = strSQL_Info + " (Customer.AddressUID IN (SELECT DISTINCT AddressUID FROM dbo.tblAddress where FullAdd_Eng LIKE '%'+@Address+'%' OR FullAdd_Chi LIKE'%'+@Address+'%' ) OR ContactPerson.AddressUID IN (SELECT AddressUID FROM dbo.tblAddress where  FullAdd_Eng LIKE '%'+@Address+'%' OR FullAdd_Chi LIKE'%'+@Address+'%' )) and ";
            }
            if (ent.Country != null & ent.Country != "")
            {
                strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCust_Market where Deleted=0 and CountryUID in(SELECT CountryUID FROM  dbo.mtblCountry WHERE Deleted=0 and CONTAINS((Country_Eng, Country_Chi ),@Country))) and ";
            }
            if (ent.Position != null & ent.Position != "")
            {
                strSQL_Info = strSQL_Info + " CustContactUID IN (SELECT CustContactUID FROM dbo.tblCust_ContactPerson WHERE Deleted=0 and CONTAINS((Position_Chi,Position_Eng),@Position)) and ";
            }
            if (ent.ContactPersonName != null & ent.ContactPersonName != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.ContactPersonUID IN (select table1.ContactPersonUID from (select DISTINCT ContactPersonUID ,(ISNULL(LastName_Chi,'')+ISNULL(FirstName_Chi,''))AS FirstAndLastName_Chi,(dbo.CheckNull(ChristianName)+dbo.CheckNull(FirstName_Eng)+dbo.CheckNull(LastName_Eng)) AS FirstAndLastName_Eng from dbo.tblContactPerson) table1 where Cust_ContactPerson.Deleted=0 and table1.FirstAndLastName_Chi like '%'+@ContactPersonName+'%' OR table1.FirstAndLastName_Eng like '%'+@ContactPersonName+'%')) and ";
            }
            if (ent.Salutation != null & ent.Salutation != "")
            {
                strSQL_Info = strSQL_Info + " ContactPerson.SalutationUID IN (SELECT DISTINCT SalutationUID FROM dbo.mtblSalutation where Deleted=0 and CONTAINS((Salutation_Eng,Salutation_Chi),@Salutation)) and ";
            }
            if (ent.CommitteeType != null & ent.CommitteeType != "")
            {
                strSQL_Info = strSQL_Info + " Cperson_CommMemberUID IN (SELECT Cperson_CommMemberUID FROM dbo.tblCPerson_CommMember WHERE Deleted=0 and CommTypeUID IN(SELECT CommTypeUID FROM dbo.mtblCommType WHERE CommType_Eng=@CommitteeType)) and ";
            }
            if (ent.CommitteePosition != null & ent.CommitteePosition != "")
            {
                strSQL_Info = strSQL_Info + " CPerson_CommMemberUID IN (SELECT Cperson_CommMemberUID FROM dbo.tblCPerson_CommMember WHERE Deleted=0 and CommPositionUID IN(SELECT CommPositionUID FROM dbo.mtblCommPosition WHERE CONTAINS((CommPosition_Eng,CommPosition_Chi),@CommitteePosition))) and ";
            }
            if (ent.YearTerm != null & ent.YearTerm != "")
            {
                strSQL_Info = strSQL_Info + " Cperson_CommMemberUID in (SELECT Cperson_CommMemberUID FROM dbo.tblCPerson_CommMember WHERE deleted=0 and CONTAINS([YEAR],@YearTerm)) and ";
            }
            if (ent.ProductSpecifictions != null & ent.ProductSpecifictions != "")
            {
                strSQL_Info = strSQL_Info + " TABLe_Industry.Cust_ProdCatUID IN (SELECT DISTINCT Cust_ProdCatUID FROM dbo.tblCust_ProdCat WHERE Deleted=0 and ProductSubCatUID IN (SELECT ProductSubCatUID FROM dbo.mtblProductSubCat where CONTAINS	((ProductSubCat_Eng,ProductSubCat_Chi),@ProductSpecifictions))) and ";
            }
            if (ent.BranchAddress != null & ent.BranchAddress != "")
            {
                strSQL_Info = strSQL_Info + " Cust_Branch.AddressUID IN (SELECT DISTINCT AddressUID FROM dbo.tblAddress where CONTAINS((Area_Eng,District_Eng,City_Eng,Province_Eng,Area_Chi,District_Chi,City_Chi,Province_Chi,FullAdd_Eng,FullAdd_Chi),@BranchAddress)) and ";
            }
            if (ent.BranchName != null & ent.BranchName != "")
            {
                //strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT CustomerUID FROM dbo.tblCust_Branch WHERE Deleted=0 and CONTAINS((BranchName_Eng,BranchName_Chi),@BranchName)) and ";
                strSQL_Info = strSQL_Info + " Cust_Branch.CustBranchUID IN (SELECT CustBranchUID FROM dbo.tblCust_Branch WHERE Deleted=0 and CONTAINS((BranchName_Eng,BranchName_Chi),@BranchName)) and ";
            }
            if (ent.Email != null & ent.Email != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE Email1 LIKE '%'+@Email+'%' OR Email2 LIKE '%'+@Email+'%' OR Email3  LIKE '%'+@Email+'%'OR Email4	LIKE '%'+@Email+'%'OR  Email5 LIKE '%'+@Email+'%')OR Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Email1 LIKE '%'+@Email+'%' OR Email2 LIKE '%'+@Email+'%' OR Email3  LIKE '%'+@Email+'%'OR Email4 LIKE '%'+@Email+'%'OR Email5 LIKE '%'+@Email+'%')OR ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE Email1 LIKE '%'+@Email+'%' OR Email2 LIKE '%'+@Email+'%' OR Email3  LIKE '%'+@Email+'%'OR Email4	LIKE '%'+@Email+'%'OR  Email5 LIKE '%'+@Email+'%')) and ";
            }

            if (ent.EmailUnsubscribe != null & ent.EmailUnsubscribe != "")
            {
                strSQL_Info = strSQL_Info + " (Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Email1_IsSubscribe=@EmailUnsubscribe OR Email2_IsSubscribe=@EmailUnsubscribe OR Email3_IsSubscribe=@EmailUnsubscribe OR Email4_IsSubscribe=@EmailUnsubscribe OR Email5_IsSubscribe=@EmailUnsubscribe) OR ContactPerson.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Email1_IsSubscribe=@EmailUnsubscribe OR Email2_IsSubscribe=@EmailUnsubscribe OR Email3_IsSubscribe=@EmailUnsubscribe OR Email4_IsSubscribe=@EmailUnsubscribe OR Email5_IsSubscribe=@EmailUnsubscribe)OR Cust_Branch.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Email1_IsSubscribe=@EmailUnsubscribe OR Email2_IsSubscribe=@EmailUnsubscribe OR Email3_IsSubscribe=@EmailUnsubscribe OR Email4_IsSubscribe=@EmailUnsubscribe OR Email5_IsSubscribe=@EmailUnsubscribe)) and ";
            }

            if (ent.Fax != null & ent.Fax != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE Fax1 LIKE '%'+@Fax+'%' OR Fax2 LIKE '%'+@Fax+'%' OR Fax3  LIKE '%'+@Fax+'%'OR Fax4	LIKE '%'+@Fax+'%'OR  Fax5 LIKE '%'+@Fax+'%')OR Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1 LIKE '%'+@Fax+'%' OR Fax2 LIKE '%'+@Fax+'%' OR Fax3  LIKE '%'+@Fax+'%'OR Fax4 LIKE '%'+@Fax+'%'OR Fax5 LIKE '%'+@Fax+'%')OR ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE Fax1 LIKE '%'+@Fax+'%' OR Fax2 LIKE '%'+@Fax+'%' OR Fax3  LIKE '%'+@Fax+'%'OR Fax4	LIKE '%'+@Fax+'%'OR  Fax5 LIKE '%'+@Fax+'%')) and ";
            }

            if (ent.FaxUnsubscribe != null & ent.FaxUnsubscribe != "")
            {
                strSQL_Info = strSQL_Info + " (Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1_IsSubscribe=@FaxUnsubscribe OR Fax2_IsSubscribe=@FaxUnsubscribe OR Fax3_IsSubscribe=@FaxUnsubscribe OR Fax4_IsSubscribe=@FaxUnsubscribe OR Fax5_IsSubscribe=@FaxUnsubscribe) OR ContactPerson.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1_IsSubscribe=@FaxUnsubscribe OR Fax2_IsSubscribe=@FaxUnsubscribe OR Fax3_IsSubscribe=@FaxUnsubscribe OR Fax4_IsSubscribe=@FaxUnsubscribe OR Fax5_IsSubscribe=@FaxUnsubscribe) OR Cust_Branch.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1_IsSubscribe=@FaxUnsubscribe OR Fax2_IsSubscribe=@FaxUnsubscribe OR Fax3_IsSubscribe=@FaxUnsubscribe OR Fax4_IsSubscribe=@FaxUnsubscribe OR Fax5_IsSubscribe=@FaxUnsubscribe)) and ";
            }
            if (ent.Fax_IsDNC != null & ent.Fax_IsDNC != "")
            {
                strSQL_Info = strSQL_Info + " (Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1_IsDNC=@Fax_IsDNC OR Fax2_IsDNC=@Fax_IsDNC OR Fax3_IsDNC=@Fax_IsDNC OR Fax4_IsDNC=@Fax_IsDNC OR Fax5_IsDNC=@Fax_IsDNC) OR ContactPerson.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1_IsDNC=@Fax_IsDNC OR Fax2_IsDNC=@Fax_IsDNC OR Fax3_IsDNC=@Fax_IsDNC OR Fax4_IsDNC=@Fax_IsDNC OR Fax5_IsDNC=@Fax_IsDNC) OR Cust_Branch.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Fax1_IsDNC=@Fax_IsDNC OR Fax2_IsDNC=@Fax_IsDNC OR Fax3_IsDNC=@Fax_IsDNC OR Fax4_IsDNC=@Fax_IsDNC OR Fax5_IsDNC=@Fax_IsDNC)) and ";
            }
            if (ent.Tel != null & ent.Tel != "")
            {
                strSQL_Info = strSQL_Info + " (ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE Tel1 LIKE '%'+@Tel+'%' OR Tel2 LIKE '%'+@Tel+'%' OR Tel3  LIKE '%'+@Tel+'%'OR Tel4	LIKE '%'+@Tel+'%'OR  Tel5 LIKE '%'+@Tel+'%')OR Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1 LIKE '%'+@Tel+'%' OR Tel2 LIKE '%'+@Tel+'%' OR Tel3  LIKE '%'+@Tel+'%'OR Tel4 LIKE '%'+@Tel+'%'OR Tel5 LIKE '%'+@Tel+'%')OR  ContactPerson.ContactInfoUID IN (SELECT DISTINCT ContactInfoUID  FROM dbo.tblContactInfo WHERE Tel1 LIKE '%'+@Tel+'%' OR Tel2 LIKE '%'+@Tel+'%' OR Tel3  LIKE '%'+@Tel+'%'OR Tel4	LIKE '%'+@Tel+'%'OR  Tel5 LIKE '%'+@Tel+'%')) and ";
            }

            if (ent.Tel_IsSubscribe != null & ent.Tel_IsSubscribe != "")
            {
                strSQL_Info = strSQL_Info + " (Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1_IsSubscribe=@Tel_IsSubscribe OR Tel2_IsSubscribe=@Tel_IsSubscribe OR Tel3_IsSubscribe=@Tel_IsSubscribe OR Tel4_IsSubscribe=@Tel_IsSubscribe OR Tel5_IsSubscribe=@Tel_IsSubscribe) OR ContactPerson.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1_IsSubscribe=@Tel_IsSubscribe OR Tel2_IsSubscribe=@Tel_IsSubscribe OR Tel3_IsSubscribe=@Tel_IsSubscribe OR Tel4_IsSubscribe=@Tel_IsSubscribe OR Tel5_IsSubscribe=@Tel_IsSubscribe) OR Cust_Branch.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1_IsSubscribe=@Tel_IsSubscribe OR Tel2_IsSubscribe=@Tel_IsSubscribe OR Tel3_IsSubscribe=@Tel_IsSubscribe OR Tel4_IsSubscribe=@Tel_IsSubscribe OR Tel5_IsSubscribe=@Tel_IsSubscribe)) and ";
            }

            if (ent.Tel_IsDNC != null & ent.Tel_IsDNC != "")
            {
                strSQL_Info = strSQL_Info + " (Customer.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1_IsDNC=@Tel_IsDNC OR Tel2_IsDNC=@Tel_IsDNC OR Tel3_IsDNC=@Tel_IsDNC OR Tel4_IsDNC=@Tel_IsDNC OR Tel5_IsDNC=@Tel_IsDNC) OR ContactPerson.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1_IsDNC=@Tel_IsDNC OR Tel2_IsDNC=@Tel_IsDNC OR Tel3_IsDNC=@Tel_IsDNC OR Tel4_IsDNC=@Tel_IsDNC OR Tel5_IsDNC=@Tel_IsDNC) OR Cust_Branch.ContactInfoUID IN (SELECT ContactInfoUID FROM dbo.tblContactInfo WHERE Tel1_IsDNC=@Tel_IsDNC OR Tel2_IsDNC=@Tel_IsDNC OR Tel3_IsDNC=@Tel_IsDNC OR Tel4_IsDNC=@Tel_IsDNC OR Tel5_IsDNC=@Tel_IsDNC)) and ";
            }

            if (ent.Attribute != null & ent.Attribute != "")
            {
                strSQL_Info = strSQL_Info + " Cust_ContactPerson.ContactPersonUID IN(SELECT DISTINCT ContactPersonUID FROM dbo.tblCPerson_Attribute WHERE Deleted=0 and AttributeUID IN ("+Common.CheckParams(ent.Attribute)+")) and ";
            }
            if (ent.JobNaturexdcc != null & ent.JobNaturexdcc != "")
            {
                strSQL_Info = strSQL_Info + " Cust_ContactPerson.ContactPersonUID IN(SELECT DISTINCT ContactPersonUID FROM dbo.tblCPerson_JobNature WHERE Deleted=0 and JobNatureUID IN (" + Common.CheckParams(ent.JobNaturexdcc) + ")) and ";
            }
            if (ent.BusinessNatur != null & ent.BusinessNatur != "")
            {
                if (ent.BusSelVal == "or")
                {
                    strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCust_BusNature WHERE Deleted=0 and BusNatureUID IN ("+Common.CheckParams(ent.BusinessNatur)+")) and ";
                }
                else if (ent.BusSelVal == "and")
                {
                    strSQL_Info = strSQL_Info + " (TABLe_Industry.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCust_ProdCat WHERE Deleted=0 and ProductSubCatUID IN ("+Common.CheckParams(ent.BusinessNatur)+") group by CustomerUID having count(CustomerUID)=@BusNum) AND TABLe_Industry.ProductCatID IN(SELECT DISTINCT ProductCat.ProductCatID FROM dbo.mtblProductCat AS ProductCat LEFT JOIN dbo.tblCust_ProdCat AS Cust_ProdCat ON ProductCat.ProductCatUID = Cust_ProdCat.ProductCatUID WHERE Cust_ProdCat.ProductSubCatUID IN ("+Common.CheckParams(ent.BusinessNatur)+"))) and ";
                }
            }
            if (ent.ProductCategory != null & ent.ProductCategory != "")
            {
                if (ent.ProSelVal == "or")
                {
                    strSQL_Info = strSQL_Info + " TABLe_Industry.Cust_ProdCatUID IN (SELECT  DISTINCT Cust_ProdCatUID FROM dbo.tblCust_ProdCat WHERE Deleted=0 and ProductSubCatUID IN(" + Common.CheckParams(ent.ProductCategory) + ")) and ";
                }
                else if (ent.ProSelVal == "and")
                {
                    strSQL_Info = strSQL_Info + " (TABLe_Industry.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCust_ProdCat WHERE Deleted=0 and ProductSubCatUID IN (" + Common.CheckParams(ent.ProductCategory) + ") group by CustomerUID having count(CustomerUID)=@ProNum) AND TABLe_Industry.ProductCatID IN(SELECT DISTINCT ProductCat.ProductCatID FROM dbo.mtblProductCat AS ProductCat LEFT JOIN dbo.tblCust_ProdCat AS Cust_ProdCat ON ProductCat.ProductCatUID = Cust_ProdCat.ProductCatUID WHERE Cust_ProdCat.ProductSubCatUID IN (" + Common.CheckParams(ent.ProductCategory) + "))) and ";
                }
            }
            if (ent.MainMarket != null & ent.MainMarket != "")
            {
                if (ent.MarketSelVal == "or")
                {
                    strSQL_Info = strSQL_Info + " Customer.CustomerUID IN (SELECT DISTINCT CustomerUID FROM dbo.tblCust_Market WHERE Deleted=0 and CountryUID IN ("+Common.CheckParams(ent.MainMarket)+")) and ";
                }
                else if (ent.MarketSelVal == "and")
                {
                    strSQL_Info = strSQL_Info + " Customer.CustomerUID IN(SELECT DISTINCT  V.Cid FROM (SELECT DISTINCT CustomerUID AS Cid,CountryUID AS Aid FROM dbo.tblCust_Market WHERE Deleted=0 and CountryUID IN(" + Common.CheckParams(ent.MainMarket) + "))AS V group by V.Cid having COUNT (V.Cid)=@MarketNum) and ";
                }
            }

            strSQL_Info = strSQL_Info.Substring(0, strSQL_Info.Length - 5);
            string strSQL_C=" order by Customer.CustomerID,CustType.CustType_Chi,Customer.Name_Eng,ContactPerson.FirstName_Chi,ContactPerson.LastName_Chi,ContactPerson.FirstName_Eng,ContactPerson.LastName_Eng,ContactPerson.ChristianName";
            str.Append(SqlStr);
            str.Append(strSQL_Info + strSQL_C);
            return str.ToString();
        }
        #endregion
    }
}
